﻿Imports System.Web.Services
Imports System.Web.Services.Protocols
Imports System.ComponentModel
Imports MySql.Data
Imports MySql.Data.MySqlClient
Imports Microsoft.VisualBasic.CompilerServices

' To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.
' <System.Web.Script.Services.ScriptService()> _
<System.Web.Services.WebService(Namespace:="http://tempuri.org/")> _
<System.Web.Services.WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _
<ToolboxItem(False)> _
Public Class Service1
    Inherits System.Web.Services.WebService

    Dim connstr As String = ConfigurationManager.ConnectionStrings("MySQLConnString").ConnectionString


    <WebMethod(Description:="Test the Web Service. Return HelloWorld string.")> _
    Public Function HelloWorld() As String
        Return "Hello World"
    End Function
    <WebMethod(Description:="Test the Web Service with input.")> _
    Public Function GetHelloWithParam(ByVal vInputName As String) As String
        Return "Hello, " + vInputName
    End Function

#Region " ~ Manage data ~ "

    <WebMethod(Description:="Display data from main table.")> _
    Public Function DisplayData() As DataTable
        Dim cnMySQL As New MySqlConnection(connstr)
        Dim cmdMySQL As MySqlCommand = cnMySQL.CreateCommand
        cmdMySQL.CommandText = "select * from bullseye"
        cnMySQL.Open()
        Dim reader As MySqlDataReader = cmdMySQL.ExecuteReader
        Dim dt As New DataTable
        dt.Load(reader)
        cnMySQL.Close()
        Return dt
    End Function

    <WebMethod(Description:="Test the MySQL connection with the database.")> _
    Public Function TestMySQLConnection() As String
        Dim ResultConnection As String
        Try
            Dim connection As New MySqlConnection(connstr)
            connection.Open()
            connection.Close()
            ResultConnection = "Connection with MySQL DB is OK."
        Catch exception1 As Exception
            ProjectData.SetProjectError(exception1)
            Dim ex As Exception = exception1
            ResultConnection = ex.Message
            ProjectData.ClearProjectError()
            Return ResultConnection
            ProjectData.ClearProjectError()
        End Try
        Return ResultConnection
    End Function

    <WebMethod(Description:="Adding Details Main Table.")> _
    Public Function InsertData(ByVal user As String, ByVal points As Integer, ByVal rounds As Integer)
        Dim strSQL As String = ""
        Dim RecordsInserted As Integer
        Dim cnMySQL As New MySqlConnection(connstr)
        Dim cmdMySQL As MySqlCommand = cnMySQL.CreateCommand

        strSQL = "INSERT INTO bullseye (user, points, rounds) VALUES ('" & user & "', " & points & ", " & rounds & ")"
        Using cnMySQL
            Try
                cnMySQL.Open()
                cmdMySQL.CommandText = strSQL
                RecordsInserted = cmdMySQL.ExecuteNonQuery()
                cnMySQL.Close()
            Catch ex As Exception
                Return RecordsInserted
            End Try
        End Using
        Return RecordsInserted
    End Function

    <WebMethod(Description:="Delete record from Main table by RecID")> _
    Public Function DeleteData(ByVal vRecID As Integer)
        Dim strSQL As String = ""
        Dim RecordsDeleted As Integer
        Dim cnMySQL As New MySqlConnection(connstr)
        Dim cmdMySQL As MySqlCommand = cnMySQL.CreateCommand

        strSQL = "DELETE FROM bullseye WHERE IDRec = " & vRecID
        Using cnMySQL
            Try
                cnMySQL.Open()
                cmdMySQL.CommandText = strSQL
                RecordsDeleted = cmdMySQL.ExecuteNonQuery()
                cnMySQL.Close()
            Catch ex As Exception
                Return RecordsDeleted
            End Try
        End Using
        Return RecordsDeleted
    End Function

    <WebMethod(Description:="Update record from Main table by RecID")> _
    Public Function UpdateData(ByVal vRecID As Integer, ByVal vUser As String, ByVal vPoints As Integer, ByVal vRounds As Integer)
        Dim strSQL As String = ""
        Dim RecordsUpdated As Integer
        Dim cnMySQL As New MySqlConnection(connstr)
        Dim cmdMySQL As MySqlCommand = cnMySQL.CreateCommand

        strSQL = "UPDATE bullseye SET user = '" & vUser & "', points = " & vPoints & ", rounds = " & vRounds & " WHERE IDRec = " & vRecID
        Using cnMySQL
            Try
                cnMySQL.Open()
                cmdMySQL.CommandText = strSQL
                RecordsUpdated = cmdMySQL.ExecuteNonQuery()
                cnMySQL.Close()
            Catch ex As Exception
                Return RecordsUpdated
            End Try
        End Using
        Return RecordsUpdated
    End Function

#End Region


End Class